package com.zl.learning.db.sqlite;

import org.junit.BeforeClass;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.function.Consumer;

public class SQLiteTest2 {

    private static Connection conn;

    @BeforeClass
    public static void init() {
        try {
            Class.forName("org.sqlite.JDBC");
//            conn = DriverManager.getConnection("jdbc:sqlite:test.db");
            conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private void initTable() {
        String sql = "CREATE TABLE \"tbl_hotspot\" (\n" +
                "  \"person_Id\" VARCHAR(50) NOT NULL,\n" +
                "  \"capture_time\" DATETIME NOT NULL,\n" +
                "  \"device_id\" VARCHAR(50) NOT NULL,\n" +
                "  \"mnc\" VARCHAR(50),\n" +
                "  \"own_area\" VARCHAR(50),\n" +
                "  \"tele_seven_num\" VARCHAR(7),\n" +
                "  PRIMARY KEY (\"person_Id\", \"capture_time\", \"device_id\")\n" +
                ")";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void execute(Consumer<PreparedStatement> consumer,String sql){
        try (PreparedStatement pstmt = conn.prepareStatement(sql)){
            consumer.accept(pstmt);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void test1() {
        initTable();
        String sql = "INSERT or  IGNORE INTO \"main\".\"tbl_hotspot\"(\"person_Id\", \"capture_time\", \"device_id\", \"mnc\", \"own_area\", \"tele_seven_num\") VALUES ('460090220112964', '2019-01-02 11:12:10', '2019100', NULL, NULL, NULL);";
        execute(pstmt-> {
            try {
                pstmt.executeUpdate();
                pstmt.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        },sql);
    }
}
